﻿using Microsoft.Office.Interop.Excel;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Diagnostics;
using System.IO;
using System.Linq;
using System.Text;
using System.Windows.Forms;
namespace FileCopy
{
     //Excel载入器
    class ExcelLoader
    {
        mainForm myForm; //主界面
        string path; //路径
        DataGridView dgv;
        delegate void SetTextCallback(string status,int done,int total,string [] rows);//设置委托
        //初始化
        public ExcelLoader(mainForm _myForm,string _path,DataGridView _dgv)
        {
            myForm = _myForm;
            path = _path;
            dgv=_dgv;
        }
        public void Load()
        {
            try
            {
                Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
                if (excel == null)
                {
                    MessageBox.Show("无法创建Excel对象,可能您的计算机未安装Excel!");
                    return;
                }
                excel.Visible = false;
                excel.UserControl = true;
                object oMissiong = System.Reflection.Missing.Value;
                excel.Application.Workbooks.Open(path);
                Microsoft.Office.Interop.Excel.Worksheet worksheet=(Microsoft.Office.Interop.Excel.Worksheet)excel.Worksheets.get_Item(1);
                int rowCount = worksheet.UsedRange.Cells.Rows.Count;
                int colCount = worksheet.UsedRange.Cells.Columns.Count;
                int minCount = colCount > dgv.Columns.Count ? dgv.Columns.Count : colCount;
                string[] row_data= new string[minCount];
                Range rng1 = worksheet.Cells.get_Range(int2ASCII(1)+"2", int2ASCII(minCount)+ rowCount); 
                object[,] arryItem = (object[,])rng1.Value2;   //get range's value
                //按每行数据进行处理
                for (int i = 1; i <=rowCount-1; i++)
                {
                    for(int j=1;j<=minCount;j++){
                       
                        row_data[j-1] = arryItem[i,j].ToString();
                    }
                    setWorkStatus("第"+i+"行",i, rowCount-1,row_data);  
                }
                excel.Quit();
                excel = null;
                /*其实不用杀死进程，当程序退出时候自动会结束进程的，杀死反而会造成excel的未保存错误
                Process[] procs = Process.GetProcessesByName("excel");
                foreach (Process pro in procs)
                {
                    pro.Kill();//没有更好的方法,只有杀掉进程
                }
                 * */
                GC.Collect();
            }
            catch (Exception ex)
            {
                setWorkStatus("提取出错！"+ex.Message.ToString(), 0,0,null);
            }            
        }
        public void setWorkStatus(string status,int done,int total,string[] rows)
        {
            //来设置工作状态，解决多线程下不能设置问题
            try
            {
                if (myForm.InvokeRequired)
                {
                    SetTextCallback d = new SetTextCallback(setWorkStatus);
                    myForm.Invoke(d, new object[] { status, done,total,rows });

                }
                else
                {
                    if (total == 0)
                    {
                        myForm.tsslStatus.Text = status;
                    }
                    else
                    {
                        //添加到dgv中
                        dgv.Rows.Add(rows);

                        if(total==done)
                            myForm.tsslStatus.Text = "载入已完成，共有" + total + "条记录。";
                        else
                            myForm.tsslStatus.Text = status + "正在载入，完成" +done+"/"+ total + "。";
                    }
                }
            }
            catch (Exception ex)
            {
                string info = ex.Message + ex.Data;

                MessageBox.Show(info);

            }

        }
        /// <summary>
        /// 将数字序号转换成大写字母序号，字母从A开始。
        /// </summary>
        /// <param name="number"></param>
        /// <returns></returns>
        private string int2ASCII(int number)
        {
            number = number + 64; //从大写字母A开始
            byte[] array = new byte[1];
            array[0] = (byte)(Convert.ToInt32(number));//ASCII码强制转换二进制
            return Convert.ToString(System.Text.Encoding.ASCII.GetString(array));
        }
    }
}
